const express = require('express');
const router = express.Router();
const { pool } = require('../database');

// 获取预约列表
router.get('/', async (req, res) => {
  try {
    const { doctor_id, patient_phone, status, wechat_id, appointment_date } = req.query;
    
    let sql = `
      SELECT a.id, a.doctor_id, a.appointment_date, a.time_slot_type, a.patient_name,
             a.patient_phone, a.symptoms, a.status, a.order_time, a.wechat_id,
             a.wechat_avatar, a.pool_id, a.created_at, a.updated_at,
             d.name as doctor_name, h.name as hospital_name, dep.name as department_name
      FROM appointments a
      LEFT JOIN doctors d ON a.doctor_id = d.id
      LEFT JOIN hospitals h ON d.hospital_id = h.id
      LEFT JOIN departments dep ON d.department_id = dep.id
      WHERE 1=1
    `;
    
    const params = [];
    
    if (doctor_id) {
      sql += ' AND a.doctor_id = ?';
      params.push(doctor_id);
    }
    
    if (patient_phone) {
      sql += ' AND a.patient_phone = ?';
      params.push(patient_phone);
    }
    
    if (status) {
      sql += ' AND a.status = ?';
      params.push(status);
    }
    
    if (wechat_id) {
      sql += ' AND a.wechat_id = ?';
      params.push(wechat_id);
    }
    
    if (appointment_date) {
      sql += ' AND a.appointment_date = ?';
      params.push(appointment_date);
    }
    
    sql += ' ORDER BY a.order_time DESC';
    
    const [rows] = await pool.execute(sql, params);
    
    res.json({
      success: true,
      data: rows,
      message: '获取预约列表成功'
    });
  } catch (error) {
    console.error('获取预约列表失败:', error);
    res.status(500).json({
      success: false,
      message: '获取预约列表失败',
      error: error.message
    });
  }
});

// 根据ID获取预约详情
router.get('/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const [rows] = await pool.execute(`
      SELECT a.id, a.doctor_id, a.appointment_date, a.time_slot_type, a.patient_name,
             a.patient_phone, a.symptoms, a.status, a.order_time, a.wechat_id,
             a.wechat_avatar, a.pool_id, a.created_at, a.updated_at,
             d.name as doctor_name, h.name as hospital_name, dep.name as department_name
      FROM appointments a
      LEFT JOIN doctors d ON a.doctor_id = d.id
      LEFT JOIN hospitals h ON d.hospital_id = h.id
      LEFT JOIN departments dep ON d.department_id = dep.id
      WHERE a.id = ?
    `, [id]);
    
    if (rows.length === 0) {
      return res.status(404).json({
        success: false,
        message: '预约不存在'
      });
    }
    
    res.json({
      success: true,
      data: rows[0],
      message: '获取预约详情成功'
    });
  } catch (error) {
    console.error('获取预约详情失败:', error);
    res.status(500).json({
      success: false,
      message: '获取预约详情失败',
      error: error.message
    });
  }
});

// 创建预约
router.post('/', async (req, res) => {
  try {
    const {
      doctor_id, appointment_date, time_slot_type, patient_name, patient_phone,
      symptoms, wechat_id, wechat_avatar
    } = req.body;
    
    // 验证必填字段
    if (!doctor_id || !appointment_date || !time_slot_type || !patient_name || !patient_phone) {
      return res.status(400).json({
        success: false,
        message: '医生ID、预约日期、时段类型、患者姓名和电话为必填项'
      });
    }
    
    // 验证医生是否存在且可用
    const [doctor] = await pool.execute(`
      SELECT id, is_available FROM doctors WHERE id = ? AND is_available = TRUE
    `, [doctor_id]);
    
    if (doctor.length === 0) {
      return res.status(400).json({
        success: false,
        message: '指定的医生不存在或不可用'
      });
    }
    
    // 验证时段类型
    if (![1, 2].includes(time_slot_type)) {
      return res.status(400).json({
        success: false,
        message: '时段类型必须为1(上午)或2(下午)'
      });
    }
    
    // 查找对应的号池
    const [pool] = await pool.execute(`
      SELECT id, available_quota FROM appointment_pools 
      WHERE doctor_id = ? AND appointment_date = ? AND time_slot_type = ? AND is_available = TRUE
    `, [doctor_id, appointment_date, time_slot_type]);
    
    if (pool.length === 0) {
      return res.status(400).json({
        success: false,
        message: '该医生在指定日期和时段没有可用的号池'
      });
    }
    
    if (pool[0].available_quota <= 0) {
      return res.status(400).json({
        success: false,
        message: '该时段号源已满，无法预约'
      });
    }
    
    // 开始事务
    const connection = await pool.getConnection();
    await connection.beginTransaction();
    
    try {
      // 创建预约
      const [result] = await connection.execute(`
        INSERT INTO appointments (doctor_id, appointment_date, time_slot_type, patient_name,
                                patient_phone, symptoms, wechat_id, wechat_avatar, pool_id)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
      `, [
        doctor_id, appointment_date, time_slot_type, patient_name,
        patient_phone, symptoms || null, wechat_id || null, wechat_avatar || null, pool[0].id
      ]);
      
      // 更新号池使用数量
      await connection.execute(`
        UPDATE appointment_pools SET used_quota = used_quota + 1 WHERE id = ?
      `, [pool[0].id]);
      
      await connection.commit();
      
      res.status(201).json({
        success: true,
        data: { id: result.insertId },
        message: '创建预约成功'
      });
    } catch (error) {
      await connection.rollback();
      throw error;
    } finally {
      connection.release();
    }
  } catch (error) {
    console.error('创建预约失败:', error);
    res.status(500).json({
      success: false,
      message: '创建预约失败',
      error: error.message
    });
  }
});

// 更新预约状态
router.patch('/:id/status', async (req, res) => {
  try {
    const { id } = req.params;
    const { status } = req.body;
    
    // 验证状态值
    const validStatuses = ['pending', 'confirmed', 'cancelled', 'completed'];
    if (!validStatuses.includes(status)) {
      return res.status(400).json({
        success: false,
        message: '状态值必须为: pending, confirmed, cancelled, completed'
      });
    }
    
    // 检查预约是否存在
    const [existing] = await pool.execute(`
      SELECT id, pool_id, status FROM appointments WHERE id = ?
    `, [id]);
    
    if (existing.length === 0) {
      return res.status(404).json({
        success: false,
        message: '预约不存在'
      });
    }
    
    // 开始事务
    const connection = await pool.getConnection();
    await connection.beginTransaction();
    
    try {
      // 更新预约状态
      await connection.execute(`
        UPDATE appointments SET status = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?
      `, [status, id]);
      
      // 如果取消预约，需要释放号池配额
      if (status === 'cancelled' && existing[0].status !== 'cancelled') {
        await connection.execute(`
          UPDATE appointment_pools SET used_quota = used_quota - 1 WHERE id = ?
        `, [existing[0].pool_id]);
      }
      
      // 如果从取消状态恢复，需要重新占用号池配额
      if (existing[0].status === 'cancelled' && status !== 'cancelled') {
        await connection.execute(`
          UPDATE appointment_pools SET used_quota = used_quota + 1 WHERE id = ?
        `, [existing[0].pool_id]);
      }
      
      await connection.commit();
      
      res.json({
        success: true,
        message: '更新预约状态成功'
      });
    } catch (error) {
      await connection.rollback();
      throw error;
    } finally {
      connection.release();
    }
  } catch (error) {
    console.error('更新预约状态失败:', error);
    res.status(500).json({
      success: false,
      message: '更新预约状态失败',
      error: error.message
    });
  }
});

// 更新预约信息
router.put('/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const {
      appointment_date, time_slot_type, patient_name, patient_phone, symptoms
    } = req.body;
    
    // 检查预约是否存在
    const [existing] = await pool.execute(`
      SELECT id, doctor_id, pool_id, status FROM appointments WHERE id = ?
    `, [id]);
    
    if (existing.length === 0) {
      return res.status(404).json({
        success: false,
        message: '预约不存在'
      });
    }
    
    // 如果预约已取消或完成，不允许修改
    if (['cancelled', 'completed'].includes(existing[0].status)) {
      return res.status(400).json({
        success: false,
        message: '已取消或已完成的预约不允许修改'
      });
    }
    
    // 验证时段类型
    if (time_slot_type && ![1, 2].includes(time_slot_type)) {
      return res.status(400).json({
        success: false,
        message: '时段类型必须为1(上午)或2(下午)'
      });
    }
    
    await pool.execute(`
      UPDATE appointments SET
        appointment_date = ?, time_slot_type = ?, patient_name = ?,
        patient_phone = ?, symptoms = ?, updated_at = CURRENT_TIMESTAMP
      WHERE id = ?
    `, [
      appointment_date, time_slot_type, patient_name,
      patient_phone, symptoms, id
    ]);
    
    res.json({
      success: true,
      message: '更新预约信息成功'
    });
  } catch (error) {
    console.error('更新预约信息失败:', error);
    res.status(500).json({
      success: false,
      message: '更新预约信息失败',
      error: error.message
    });
  }
});

// 删除预约
router.delete('/:id', async (req, res) => {
  try {
    const { id } = req.params;
    
    // 检查预约是否存在
    const [existing] = await pool.execute(`
      SELECT id, pool_id, status FROM appointments WHERE id = ?
    `, [id]);
    
    if (existing.length === 0) {
      return res.status(404).json({
        success: false,
        message: '预约不存在'
      });
    }
    
    // 开始事务
    const connection = await pool.getConnection();
    await connection.beginTransaction();
    
    try {
      // 删除预约
      await connection.execute('DELETE FROM appointments WHERE id = ?', [id]);
      
      // 如果预约不是取消状态，需要释放号池配额
      if (existing[0].status !== 'cancelled') {
        await connection.execute(`
          UPDATE appointment_pools SET used_quota = used_quota - 1 WHERE id = ?
        `, [existing[0].pool_id]);
      }
      
      await connection.commit();
      
      res.json({
        success: true,
        message: '删除预约成功'
      });
    } catch (error) {
      await connection.rollback();
      throw error;
    } finally {
      connection.release();
    }
  } catch (error) {
    console.error('删除预约失败:', error);
    res.status(500).json({
      success: false,
      message: '删除预约失败',
      error: error.message
    });
  }
});

// 获取预约统计信息
router.get('/stats/overview', async (req, res) => {
  try {
    const { hospital_id, doctor_id, start_date, end_date } = req.query;
    
    let sql = `
      SELECT 
        COUNT(*) as total_appointments,
        SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending_count,
        SUM(CASE WHEN status = 'confirmed' THEN 1 ELSE 0 END) as confirmed_count,
        SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) as cancelled_count,
        SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) as completed_count
      FROM appointments a
      LEFT JOIN doctors d ON a.doctor_id = d.id
      WHERE 1=1
    `;
    
    const params = [];
    
    if (hospital_id) {
      sql += ' AND d.hospital_id = ?';
      params.push(hospital_id);
    }
    
    if (doctor_id) {
      sql += ' AND a.doctor_id = ?';
      params.push(doctor_id);
    }
    
    if (start_date) {
      sql += ' AND a.appointment_date >= ?';
      params.push(start_date);
    }
    
    if (end_date) {
      sql += ' AND a.appointment_date <= ?';
      params.push(end_date);
    }
    
    const [rows] = await pool.execute(sql, params);
    
    res.json({
      success: true,
      data: rows[0],
      message: '获取预约统计信息成功'
    });
  } catch (error) {
    console.error('获取预约统计信息失败:', error);
    res.status(500).json({
      success: false,
      message: '获取预约统计信息失败',
      error: error.message
    });
  }
});

module.exports = router;
